package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;

import model.entity.Code;
import model.entity.ResourceEntity;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class ResourceDAO extends AbstractDAO<ResourceEntity> {

	private static final Logger logger = Logger.getLogger(ResourceDAO.class);

	protected static final String INSERT_RESOURCE = "INSERT INTO RESOURCES (RESOURCE_LOCATION, "
			+ "RESOURCE_LOCATION_MARKED, RESOURCE_NAME, RESOURCE_PREFIX,RESOURCE_SECUENCIAL) values (?,?,?,?,?)";

	protected static final String UPDATE_RESOURCE = "UPDATE RESOURCES SET (RESOURCE_LOCATION = ?, RESOURCE_LOCATION_MARKED = ?"
			+ "RESOURCE_NAME = ?, RESOURCE_PREFIX = ?, RESOURCE_SECUENCIAL = ? ) WHERE RESOURCE_ID = ?";

	@Override
	protected String getIdField() {
		return "RESOURCE_ID";
	}

	@Override
	protected RowMapper<ResourceEntity> getMapper() {
		return new RowMapper<ResourceEntity>() {

			@Override
			public ResourceEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
				ResourceEntity resource = new ResourceEntity();
				resource.setId(rs.getLong("resource_id"));
				resource.setLocation(rs.getString("resource_location"));
				resource.setLocationMarked(rs.getString("resource_location_marked"));
				resource.setName(rs.getString("resource_name"));
				Code code = new Code(rs.getString("resource_prefix"), rs.getInt("resource_secuencial"));
				resource.setCode(code);
				return resource;
			}
		};
	}

	@Override
	protected String getTableName() {
		return "RESOURCES";
	}

	@Override
	public ResourceEntity store(ResourceEntity object) {
		if (object != null) {
			if (object.getId() == null) {
				object = add(object);
			} else {
				object = update(object);
			}
		}
		return object;
	}

	private ResourceEntity update(final ResourceEntity object) {
		PreparedStatementSetter psc = new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				int id = ResourceDAO.this.setValues(object, ps);
				ps.setLong(++id, object.getId());
			}
		};
		jdbcTemplate.update(UPDATE_RESOURCE, psc);
		return object;
	}

	private ResourceEntity add(final ResourceEntity object) {
		PreparedStatementCreator psc = new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(INSERT_RESOURCE, Statement.RETURN_GENERATED_KEYS);
				setValues(object, ps);
				return ps;
			}
		};

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(psc, keyHolder);
		long itemId = keyHolder.getKey().longValue();
		object.setId(itemId);
		return object;
	}

	private int setValues(ResourceEntity object, PreparedStatement ps) throws SQLException {
		int i = 1;
		ps.setString(i++, object.getLocation());
		ps.setString(i++, object.getLocationMarked());
		ps.setString(i++, object.getName());
		ps.setString(i++, object.getCode().getPrefix());
		ps.setInt(i, object.getCode().getSecuencial());
		return i;
	}

	public Collection<ResourceEntity> find(String name, String prefix, Integer secuencial) {
		String sql = "select * from RESOURCES where 1=1 ";
		if(name != null && !name.isEmpty()){
			name = name.replace("*", "%");
			sql += "and resource_name like '" + name + "' ";
		}
		if(prefix != null && !prefix.isEmpty()){
			prefix = prefix.replace("*", "%");
			sql += "and resource_prefix like '" + prefix + "'";
		}
		if(secuencial != null && secuencial>0){
			sql += "and resource_secuencial = " + secuencial;
		}
		return jdbcTemplate.query(sql, getMapper());
	}

	public int getSecuencialForPrefix(String prefix) {
		return jdbcTemplate.queryForInt("select max(resource_secuencial) from RESOURCES where resource_prefix = ?", prefix);
	}
	
	public void deleteField(int id){
		String sql = "delete * from RESOURCES where id = ?";
		sql += id;
		jdbcTemplate.execute(sql);
	}

}
